package com.andy.dao;

import java.util.List;

import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;

import com.andy.dao.base.BaseHibernateDAO;
import com.andy.entity.ExamSubject;

@Repository
public class ExamSubjectDAO extends BaseHibernateDAO<ExamSubject> implements
		IExamSubjectDAO {
	
	/* (non-Javadoc) 获取所有当前科目关联的学生在DAO的查询代码
	 * @see com.andy.dao.IExamSubjectDAO#getAllUserBySubject(java.lang.Integer)
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<String> getAllUserBySubject(Integer subjectId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id = :subjectId ");
		
		return this.createSqlQuery(String.valueOf(sql))
						.addScalar("userId", StringType.INSTANCE)
						.setInteger("subjectId", subjectId)
						.list();
	}
	
//	@Override
//	@SuppressWarnings("unchecked")
//	public List<ExamUserVO> getAllUserBySubject(Integer subjectId) {
//		StringBuilder sql = new StringBuilder();
//		sql.append(" select user.user_id as userId, user.user_name as userName ");
//		sql.append(" from exam_user_subject eus ");
//		sql.append(" left join exam_subject  ");
//		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
//		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
//		sql.append(" where es.sub_id = :subjectId ");
//		
//		return this.createSqlQuery(String.valueOf(sql))
//						.addScalar("userId", StringType.INSTANCE)
//						.addScalar("userName", StringType.INSTANCE)
//						.setInteger("subjectId", subjectId)
//						.setResultTransformer(Transformers.aliasToBean(ExamUserVO.class))
//						.list();
//	}

	@Override
	public void insertSubjectUsers(List<String> insertList, Integer subjectId) {
		String sql = null;
		// 添加多条数据
		for (String userId : insertList) {
			sql = "insert into exam_user_subject(user_id, sub_id) values(:userId, :subjectId)";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
		
	}
	
	@Override
	public void deleteSubjectUsers(List<String> delList, Integer subjectId) {
		String sql = null;
		// 删除多条数据
		for (String userId : delList) {
			sql = "delete exam_user_subject from exam_user_subject where user_id = :userId and sub_id = :subjectId ";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
	}
	
}
